IF COL_LENGTH('[DW].[dItem]', 'Current') IS NULL
BEGIN
    ALTER TABLE [DW].[dItem]
    ADD [Current] INT
END

/****** Object:  StoredProcedure [dbo].[v9dimMergedItem]    Script Date: 22.11.2021 13:32:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[v9dimMergedItem] 

AS 
BEGIN

DROP TABLE IF EXISTS #TEMPstagin_item;

DECLARE @CurrentDate DATE = getdate();

WITH tMAXRowIDItem
AS
(SELECT
		sITM.[CompanyPrefix]
	   ,sITM.[No_]
	   ,MAX(sITM.[bigint_timestamp]) AS [RowID]
	FROM [dbo].[stg$Item] sITM
	GROUP BY sITM.[CompanyPrefix]
			,sITM.[No_]),

tMAXRowIDDivision
AS
(SELECT
		sDIV.[CompanyPrefix]
	   ,sDIV.[Code]
	   ,MAX(sDIV.[bigint_timestamp]) AS [RowID]
	FROM [dbo].[stg$Division] sDIV
	GROUP BY sDIV.[CompanyPrefix]
			,sDIV.[Code]),

tMAXRowIDItemCategory
AS
(SELECT
		sICA.[CompanyPrefix]
	   ,sICA.[Code]
	   ,MAX(sICA.[bigint_timestamp]) AS [RowID]
	FROM [dbo].[stg$Item Category] sICA
	GROUP BY sICA.[CompanyPrefix]
			,sICA.[Code]),

tMAXRowIDProductGroup
AS
(SELECT
		sRPG.[CompanyPrefix]
	   ,sRPG.[Code]
	   ,MAX(sRPG.[bigint_timestamp]) AS [RowID]
	FROM [dbo].[stg$Product Group] sRPG
	GROUP BY sRPG.[CompanyPrefix]
			,sRPG.[Code]),

tMAXRowIDSeasons
AS
(SELECT
		sSEA.[CompanyPrefix]
	   ,sSEA.[Code]
	   ,MAX(sSEA.[bigint_timestamp]) AS [RowID]
	FROM [dbo].[stg$Season] sSEA
	GROUP BY sSEA.[CompanyPrefix]
			,sSEA.[Code]),


tSalesPrice
AS
(SELECT
		sSAP.[CompanyPrefix]
	   ,MAX(sSAP.[bigint_timestamp]) AS [RowID]
	   ,sSAP.[Item No_]
	   ,AVG(sSAP.[Unit Price]) AS [Unit Price]
	FROM [dbo].[stg$Sales Price] sSAP
	WHERE sSAP.[Sales Code] = (SELECT TOP 1
			sRST.[Default Price Group]
		FROM [dbo].[stg$Retail Setup] sRST
		WHERE sRST.CompanyPrefix = sSAP.[CompanyPrefix])
	GROUP BY sSAP.[CompanyPrefix]
			,sSAP.[Item No_]),

tDivision
AS
(SELECT
		sDIV.[CompanyPrefix]
	   ,sDIV.[Code]
	   ,sDIV.[Description]
	   ,MAX(sDIV.[bigint_timestamp]) AS [RowID]
	FROM [stg$Division] sDIV
	RIGHT JOIN tMAXRowIDDivision tMRD
		ON sDIV.[CompanyPrefix] = tMRD.[CompanyPrefix]
		AND sDIV.[Code] = tMRD.[CODE]
		AND sDIV.[bigint_timestamp] = tMRD.[RowID]
	GROUP BY sDIV.[CompanyPrefix]
	   ,sDIV.[Code]
	   ,sDIV.[Description]),

tItemCategory
AS
(SELECT
		sICA.[CompanyPrefix]
	   ,sICA.[Code]
	   ,sICA.[Description]
	FROM [stg$Item Category] sICA
	RIGHT JOIN tMAXRowIDItemCategory tMIC
		ON sICA.[CompanyPrefix] = tMIC.[CompanyPrefix]
		AND sICA.[Code] = tMIC.[CODE]
		AND sICA.[bigint_timestamp] = tMIC.[RowID]
	GROUP BY sICA.[CompanyPrefix]
	   ,sICA.[Code]
	   ,sICA.[Description]),

tProductGroup
AS
(SELECT
		sRPG.[CompanyPrefix]
	   ,sRPG.[Code]
	   ,sRPG.[Description]
	FROM [stg$Product Group] sRPG
	RIGHT JOIN tMAXRowIDProductGroup tMRG
		ON sRPG.[CompanyPrefix] = tMRG.[CompanyPrefix]
		AND sRPG.[Code] = tMRG.[CODE]
		AND sRPG.[bigint_timestamp] = tMRG.[RowID]
	GROUP BY sRPG.[CompanyPrefix]
	   ,sRPG.[Code]
	   ,sRPG.[Description]),

tSeason
AS
(SELECT
		sSEA.[CompanyPrefix]
	   ,sSEA.[Code]
	   ,sSEA.[Description]
	   ,sSEA.[Starting Date]
	   ,sSEA.[Ending Date]
	FROM [dbo].[stg$Season] sSEA
	RIGHT JOIN tMAXRowIDSeasons AS tMSE
		ON sSEA.CompanyPrefix = tMSE.CompanyPrefix
		AND sSEA.[bigint_timestamp] = tMSE.[RowID]
		AND sSEA.[Code] = tMSE.[Code]),


tItem
AS
(SELECT
		COALESCE(dCOM.[SK_Company], -1) AS [Company]
	   ,sITM.[No_] AS [No]
	   ,sITM.[Description]
	   ,COALESCE(tITC.[Description], '') AS [Category]
	   ,COALESCE(tRPG.[Description], '') AS [ProductGroup]
	   ,sITM.[Inventory Posting Group]
	   ,sITM.[Base Unit of Measure]
	   ,sITM.[Item Capacity Value]
	   ,COALESCE(tSAP.[Unit Price], 0) AS [Unit Price]
	   ,CASE sITM.[Costing Method]
			WHEN 4 THEN COALESCE(IIF(sITM.[Standard Cost] = 0, NULL, sITM.[Standard Cost]), IIF(sITM.[Last Direct Cost] = 0, NULL, sITM.[Last Direct Cost]), IIF(sITM.[Unit Cost] = 0, NULL, sITM.[Unit Cost]), 0)
			ELSE COALESCE(IIF(sITM.[Last Direct Cost] = 0, NULL, sITM.[Last Direct Cost]), IIF(sITM.[Unit Cost] = 0, NULL, sITM.[Unit Cost]), IIF(sITM.[Standard Cost] = 0, NULL, sITM.[Standard Cost]), 0)
		END AS [LastItemCost]
	   ,sITM.[Vendor No_]
	   ,sITM.[Vendor Item No_]
	   ,COALESCE(tDIV.[Description], '') AS [DivisionName]
	   ,MAX(sITM.[bigint_timestamp]) AS [RowID]
	   ,GETUTCDATE() AS [BatchDate]
	   ,tSEA.[Description] AS [Season]

	FROM [Stg$Item] sITM
	LEFT JOIN [tItemCategory] tITC
		ON sITM.[CompanyPrefix] = tITC.[CompanyPrefix]
		AND sITM.[Item Category Code] = tITC.[Code]
	LEFT JOIN [tProductGroup] tRPG
		ON sITM.[CompanyPrefix] = tRPG.[CompanyPrefix]
		AND sITM.[Product Group Code] = tRPG.[Code]
	LEFT JOIN [tDivision] tDIV
		ON sITM.[CompanyPrefix] = tDIV.[CompanyPrefix]
		AND sITM.[Division Code] = tDIV.[Code]
	LEFT JOIN [tSalesPrice] tSAP
		ON sITM.[CompanyPrefix] = tSAP.[CompanyPrefix]
		AND sITM.No_ = tSAP.[Item No_]
	LEFT JOIN [tSeason] tSEA
		ON sITM.[CompanyPrefix] = tSEA.[CompanyPrefix]
		AND sITM.[Season Code] = tSEA.Code
	LEFT JOIN [DW].[dCompany] dCOM
		ON sITM.[CompanyPrefix] = dCOM.[CompanyPrefix]
	GROUP BY dCOM.[SK_Company]
			,sITM.[No_]
			,sITM.[Description]
			,tITC.[Description]
			,tRPG.[Description]
			,sITM.[Inventory Posting Group]
			,sITM.[Base Unit of Measure]
			,sITM.[Item Capacity Value]
			,sITM.[Unit Price]
			,COALESCE(IIF([Standard Cost] = 0, NULL, [Standard Cost]), IIF([Last Direct Cost] = 0, NULL, [Last Direct Cost]), IIF([Unit Cost] = 0, NULL, [Unit Cost]), 0)
			,COALESCE(IIF([Last Direct Cost] = 0, NULL, [Last Direct Cost]), IIF([Unit Cost] = 0, NULL, [Unit Cost]), IIF([Standard Cost] = 0, NULL, [Standard Cost]), 0)
			,sITM.[Vendor No_]
			,sITM.[Vendor Item No_]
			,tDIV.[Description]
			,tSAP.[Unit Price]
			,sITM.[Costing Method]
			,tSEA.[Description])

SELECT
	* INTO #TEMPstagin_item
FROM tItem

MERGE
INTO [DW].[dItem] USING (
	-- These rows will either UPDATE the current Items or Insert new Items
	SELECT
		tItem.[No] AS MergeNo
	   ,tItem.[Company] AS MergeCompany
	   ,'1984-05-12' AS MergeValidFrom
	   ,tItem.*
	FROM #TEMPstagin_item tItem
	UNION ALL
	-- Insert new Items for SCD2 changes
	-- Setting the mergeNo and MergeCompany to NULL forces these rows to NOT MATCH and be INSERTED
	SELECT
		NULL AS MergeNo
	   ,NULL AS MergeCompany
	   ,CAST(DATEADD(d, -1, @CurrentDate) AS DATE) AS MergeValidFrom
	   ,tItem.*
	FROM #TEMPstagin_item tItem
	JOIN [DW].[dItem]
		ON tItem.[No] = [DW].[dItem].[No]
		AND tItem.[Company] = [DW].[dItem].[Company]
	WHERE  [DW].[dItem].[Current] = 1
	AND (COALESCE(tItem.[Season], '') <> COALESCE([DW].[dItem].[Season], '')
	OR COALESCE(tItem.[Category], '') <> COALESCE([DW].[dItem].[Category], '')
	OR COALESCE(tItem.[ProductGroup], '') <> COALESCE([DW].[dItem].[ProductGroup], '')
	OR COALESCE(tItem.[DivisionName], '') <> COALESCE([DW].[dItem].Division, '')
	OR COALESCE(tItem.[Vendor No_], '') <> COALESCE([DW].[dItem].[VendorNo], ''))) 
	staged_updates
		ON [DW].[dItem].[No] = MergeNo
		AND [DW].[dItem].[Company] = MergeCompany

WHEN MATCHED
	AND [DW].[dItem].[Current] = 1
	AND (COALESCE(staged_updates.[Season], '') <> COALESCE([DW].[dItem].[Season], '')
	OR COALESCE(staged_updates.[Category], '') <> COALESCE([DW].[dItem].[scd2-Category], '')
	OR COALESCE(staged_updates.[ProductGroup], '') <> COALESCE([DW].[dItem].[scd2-ProductGroup], '')
	OR COALESCE(staged_updates.[DivisionName], '') <> COALESCE([DW].[dItem].[scd2-Division], '')
	OR COALESCE(staged_updates.[Vendor No_], '') <> COALESCE([DW].[dItem].[scd2-VendorNo], ''))

	THEN UPDATE
		SET [ValidTo] = CAST(DATEADD(d, -2, @CurrentDate) AS DATE)
		   ,[Batchdate] = staged_updates.[Batchdate]
		   ,[Current] = 0

WHEN NOT MATCHED
	THEN INSERT ([Company], [No], [Description], [Category], [ProductGroup], [InventoryPostingGroup], [BaseUnitOfMeasure], [Item Capacity Value], [UnitPrice], [LastItemCost], [VendorNo], [VendorItemNo], [Division], [RowID], [BatchDate], [Season], [scd2-Category], [scd2-ProductGroup], [scd2-Division], [scd2-VendorNo], [ValidFrom], [ValidTo], [Current])
			VALUES (staged_updates.[Company], staged_updates.[No], staged_updates.[Description], staged_updates.[Category], staged_updates.[ProductGroup], staged_updates.[Inventory Posting Group], staged_updates.[Base Unit of Measure], staged_updates.[Item Capacity Value], staged_updates.[Unit Price], staged_updates.[LastItemCost], staged_updates.[Vendor No_], staged_updates.[Vendor Item No_], staged_updates.[DivisionName], staged_updates.[RowID], staged_updates.[BatchDate], staged_updates.[Season], staged_updates.[Category], staged_updates.[ProductGroup], staged_updates.[DivisionName], staged_updates.[Vendor No_], staged_updates.MergeValidFrom, '2172-02-23', '1')

;
END

-- Update SCD type 1 columns with current value
UPDATE Dest
SET Dest.[Description] = SC.[Description]
   ,Dest.[Category] = SC.[Category]
   ,Dest.[ProductGroup] = SC.[ProductGroup]
   ,Dest.[Division] = SC.[DivisionName]
   ,Dest.[VendorNo] = SC.[Vendor No_]
   ,Dest.[VendorItemNo] = SC.[Vendor Item No_]
   ,Dest.[InventoryPostingGroup] = SC.[Inventory Posting Group]
   ,Dest.[BaseUnitOfMeasure] = SC.[Base Unit of Measure]
   ,Dest.[Item Capacity Value] = SC.[Item Capacity Value]
   ,Dest.[UnitPrice] = SC.[Unit Price]
   ,Dest.[LastItemCost] = SC.[LastItemCost]
   ,Dest.[RowID] = SC.[RowID]
   ,Dest.[BatchDate] = SC.[BatchDate]
FROM [DW].[dItem] Dest
INNER JOIN #TEMPstagin_item SC
	ON Dest.[No] = SC.[No]
	AND Dest.[Company] = SC.[Company]

SELECT
	'RowCount' = @@rowcount
GO



ALTER PROCEDURE [dbo].[v15dimMergedItem] 

AS 
BEGIN

DROP TABLE IF EXISTS #TEMPstagin_item;

DECLARE @CurrentDate DATE = getdate();

WITH tMAXRowIDItem
AS
(SELECT
		sITM.[CompanyPrefix]
	   ,sITM.[No_]
	   ,MAX(sITM.[bigint_timestamp]) AS [RowID]
	FROM [dbo].[stg$Item] sITM
	GROUP BY sITM.[CompanyPrefix]
			,sITM.[No_]),

tMAXRowIDDivision
AS
(SELECT
		sDIV.[CompanyPrefix]
	   ,sDIV.[Code]
	   ,MAX(sDIV.[bigint_timestamp]) AS [RowID]
	FROM [dbo].[stg$Division] sDIV
	GROUP BY sDIV.[CompanyPrefix]
			,sDIV.[Code]),

tMAXRowIDItemCategory
AS
(SELECT
		sICA.[CompanyPrefix]
	   ,sICA.[Code]
	   ,MAX(sICA.[bigint_timestamp]) AS [RowID]
	FROM [dbo].[stg$Item Category] sICA
	GROUP BY sICA.[CompanyPrefix]
			,sICA.[Code]),

tMAXRowIDProductGroup
AS
(SELECT
		sRPG.[CompanyPrefix]
	   ,sRPG.[Code]
	   ,MAX(sRPG.[bigint_timestamp]) AS [RowID]
	FROM [dbo].[stg$Retail Product Group] sRPG
	GROUP BY sRPG.[CompanyPrefix]
			,sRPG.[Code]),

tMAXRowIDSeasons
AS
(SELECT
		sSEA.[CompanyPrefix]
	   ,sSEA.[Code]
	   ,MAX(sSEA.[bigint_timestamp]) AS [RowID]
	FROM [dbo].[stg$Season] sSEA
	GROUP BY sSEA.[CompanyPrefix]
			,sSEA.[Code]),

tSalesPrice
AS
(SELECT
		sSAP.[CompanyPrefix]
	   ,MAX(sSAP.[bigint_timestamp]) AS [RowID]
	   ,sSAP.[Item No_]
	   ,AVG(sSAP.[Unit Price]) AS [Unit Price]
	FROM [dbo].[stg$Sales Price] sSAP
	WHERE sSAP.[Sales Code] = (SELECT TOP 1
			sRST.[Default Price Group]
		FROM [dbo].[stg$Retail Setup] sRST
		WHERE sRST.CompanyPrefix = sSAP.[CompanyPrefix])
	GROUP BY sSAP.[CompanyPrefix]
			,sSAP.[Item No_]),

tDivision
AS
(SELECT
		sDIV.[CompanyPrefix]
	   ,sDIV.[Code]
	   ,sDIV.[Description]
	   ,MAX(sDIV.[bigint_timestamp]) AS [RowID]
	FROM [stg$Division] sDIV
	RIGHT JOIN tMAXRowIDDivision tMRD
		ON sDIV.[CompanyPrefix] = tMRD.[CompanyPrefix]
		AND sDIV.[Code] = tMRD.[CODE]
		AND sDIV.[bigint_timestamp] = tMRD.[RowID]
	GROUP BY sDIV.[CompanyPrefix]
			,sDIV.[Code]
			,sDIV.[Description]),

tItemCategory
AS
(SELECT
		sICA.[CompanyPrefix]
	   ,sICA.[Code]
	   ,sICA.[Description]
	FROM [stg$Item Category] sICA
	RIGHT JOIN tMAXRowIDItemCategory tMIC
		ON sICA.[CompanyPrefix] = tMIC.[CompanyPrefix]
		AND sICA.[Code] = tMIC.[CODE]
		AND sICA.[bigint_timestamp] = tMIC.[RowID]
	GROUP BY sICA.[CompanyPrefix]
			,sICA.[Code]
			,sICA.[Description]),

tProductGroup
AS
(SELECT
		sRPG.[CompanyPrefix]
	   ,sRPG.[Code]
	   ,sRPG.[Description]
	FROM [stg$Retail Product Group] sRPG
	RIGHT JOIN tMAXRowIDProductGroup tMRG
		ON sRPG.[CompanyPrefix] = tMRG.[CompanyPrefix]
		AND sRPG.[Code] = tMRG.[CODE]
		AND sRPG.[bigint_timestamp] = tMRG.[RowID]
	GROUP BY sRPG.[CompanyPrefix]
			,sRPG.[Code]
			,sRPG.[Description]),

tSeason
AS
(SELECT
		sSEA.[CompanyPrefix]
	   ,sSEA.[Code]
	   ,sSEA.[Description]
	   ,sSEA.[Starting Date]
	   ,sSEA.[Ending Date]
	FROM [dbo].[stg$Season] sSEA
	RIGHT JOIN tMAXRowIDSeasons AS tMSE
		ON sSEA.CompanyPrefix = tMSE.CompanyPrefix
		AND sSEA.[bigint_timestamp] = tMSE.[RowID]
		AND sSEA.[Code] = tMSE.[Code]),

tItem
AS
(SELECT
		COALESCE(dCOM.[SK_Company], -1) AS [Company]
	   ,sITM.[No_] AS [No]
	   ,sITM.[Description]
	   ,COALESCE(tITC.[Description], '') AS [Category]
	   ,COALESCE(tRPG.[Description], '') AS [ProductGroup]
	   ,sITM.[Inventory Posting Group]
	   ,sITM.[Base Unit of Measure]
	   ,sITM.[Item Capacity Value]
	   ,COALESCE(tSAP.[Unit Price], 0) AS [Unit Price]
	   ,CASE sITM.[Costing Method]
			WHEN 4 THEN COALESCE(IIF(sITM.[Standard Cost] = 0, NULL, sITM.[Standard Cost]), IIF(sITM.[Last Direct Cost] = 0, NULL, sITM.[Last Direct Cost]), IIF(sITM.[Unit Cost] = 0, NULL, sITM.[Unit Cost]), 0)
			ELSE COALESCE(IIF(sITM.[Last Direct Cost] = 0, NULL, sITM.[Last Direct Cost]), IIF(sITM.[Unit Cost] = 0, NULL, sITM.[Unit Cost]), IIF(sITM.[Standard Cost] = 0, NULL, sITM.[Standard Cost]), 0)
		END AS [LastItemCost]
	   ,sITM.[Vendor No_]
	   ,sITM.[Vendor Item No_]
	   ,COALESCE(tDIV.[Description], '') AS [DivisionName]
	   ,MAX(sITM.[bigint_timestamp]) AS [RowID]
	   ,GETUTCDATE() AS [BatchDate]
	   ,tSEA.[Description] AS [Season]

	FROM [Stg$Item] sITM
	LEFT JOIN [tItemCategory] tITC
		ON sITM.[CompanyPrefix] = tITC.[CompanyPrefix]
		AND sITM.[Item Category Code] = tITC.[Code]
	LEFT JOIN [tProductGroup] tRPG
		ON sITM.[CompanyPrefix] = tRPG.[CompanyPrefix]
		AND sITM.[Retail Product Code] = tRPG.[Code]
	LEFT JOIN [tDivision] tDIV
		ON sITM.[CompanyPrefix] = tDIV.[CompanyPrefix]
		AND sITM.[Division Code] = tDIV.[Code]
	LEFT JOIN [tSalesPrice] tSAP
		ON sITM.[CompanyPrefix] = tSAP.[CompanyPrefix]
		AND sITM.No_ = tSAP.[Item No_]
	LEFT JOIN [tSeason] tSEA
		ON sITM.[CompanyPrefix] = tSEA.[CompanyPrefix]
		AND sITM.[Season Code] = tSEA.Code
	LEFT JOIN [DW].[dCompany] dCOM
		ON sITM.[CompanyPrefix] = dCOM.[CompanyPrefix]
	GROUP BY dCOM.[SK_Company]
			,sITM.[No_]
			,sITM.[Description]
			,tITC.[Description]
			,tRPG.[Description]
			,sITM.[Inventory Posting Group]
			,sITM.[Base Unit of Measure]
			,sITM.[Item Capacity Value]
			,sITM.[Unit Price]
			,COALESCE(IIF([Standard Cost] = 0, NULL, [Standard Cost]), IIF([Last Direct Cost] = 0, NULL, [Last Direct Cost]), IIF([Unit Cost] = 0, NULL, [Unit Cost]), 0)
			,COALESCE(IIF([Last Direct Cost] = 0, NULL, [Last Direct Cost]), IIF([Unit Cost] = 0, NULL, [Unit Cost]), IIF([Standard Cost] = 0, NULL, [Standard Cost]), 0)
			,sITM.[Vendor No_]
			,sITM.[Vendor Item No_]
			,tDIV.[Description]
			,tSAP.[Unit Price]
			,sITM.[Costing Method]
			,tSEA.[Description])

SELECT
	* INTO #TEMPstagin_item
FROM tItem

MERGE
INTO [DW].[dItem] USING (
	-- These rows will either UPDATE the current Items or Insert new Items
	SELECT
		tItem.[No] AS MergeNo
	   ,tItem.[Company] AS MergeCompany
	   ,'1984-05-12' AS MergeValidFrom
	   ,tItem.*
	FROM #TEMPstagin_item tItem
	UNION ALL
	-- Insert new Items for SCD2 changes
	-- Setting the mergeNo and MergeCompany to NULL forces these rows to NOT MATCH and be INSERTED
	SELECT
		NULL AS MergeNo
	   ,NULL AS MergeCompany
	   ,CAST(DATEADD(d, -1, @CurrentDate) AS DATE) AS MergeValidFrom
	   ,tItem.*
	FROM #TEMPstagin_item tItem
	JOIN [DW].[dItem]
		ON tItem.[No] = [DW].[dItem].[No]
		AND tItem.[Company] = [DW].[dItem].[Company]
	WHERE  [DW].[dItem].[Current] = 1
	AND (COALESCE(tItem.[Season], '') <> COALESCE([DW].[dItem].[Season], '')
	OR COALESCE(tItem.[Category], '') <> COALESCE([DW].[dItem].[Category], '')
	OR COALESCE(tItem.[ProductGroup], '') <> COALESCE([DW].[dItem].[ProductGroup], '')
	OR COALESCE(tItem.[DivisionName], '') <> COALESCE([DW].[dItem].Division, '')
	OR COALESCE(tItem.[Vendor No_], '') <> COALESCE([DW].[dItem].[VendorNo], ''))) 
	staged_updates
		ON [DW].[dItem].[No] = MergeNo
		AND [DW].[dItem].[Company] = MergeCompany

WHEN MATCHED
	AND [DW].[dItem].[Current] = 1
	AND (COALESCE(staged_updates.[Season], '') <> COALESCE([DW].[dItem].[Season], '')
	OR COALESCE(staged_updates.[Category], '') <> COALESCE([DW].[dItem].[scd2-Category], '')
	OR COALESCE(staged_updates.[ProductGroup], '') <> COALESCE([DW].[dItem].[scd2-ProductGroup], '')
	OR COALESCE(staged_updates.[DivisionName], '') <> COALESCE([DW].[dItem].[scd2-Division], '')
	OR COALESCE(staged_updates.[Vendor No_], '') <> COALESCE([DW].[dItem].[scd2-VendorNo], ''))

	THEN UPDATE
		SET [ValidTo] = CAST(DATEADD(d, -2, @CurrentDate) AS DATE)
		   ,[Batchdate] = staged_updates.[Batchdate]
		   ,[Current] = 0

WHEN NOT MATCHED
	THEN INSERT ([Company], [No], [Description], [Category], [ProductGroup], [InventoryPostingGroup], [BaseUnitOfMeasure], [Item Capacity Value], [UnitPrice], [LastItemCost], [VendorNo], [VendorItemNo], [Division], [RowID], [BatchDate], [Season], [scd2-Category], [scd2-ProductGroup], [scd2-Division], [scd2-VendorNo], [ValidFrom], [ValidTo], [Current])
			VALUES (staged_updates.[Company], staged_updates.[No], staged_updates.[Description], staged_updates.[Category], staged_updates.[ProductGroup], staged_updates.[Inventory Posting Group], staged_updates.[Base Unit of Measure], staged_updates.[Item Capacity Value], staged_updates.[Unit Price], staged_updates.[LastItemCost], staged_updates.[Vendor No_], staged_updates.[Vendor Item No_], staged_updates.[DivisionName], staged_updates.[RowID], staged_updates.[BatchDate], staged_updates.[Season], staged_updates.[Category], staged_updates.[ProductGroup], staged_updates.[DivisionName], staged_updates.[Vendor No_], staged_updates.MergeValidFrom, '2172-02-23', '1')

;
END

-- Update SCD type 1 columns with current value
UPDATE Dest
SET Dest.[Description] = SC.[Description]
   ,Dest.[Category] = SC.[Category]
   ,Dest.[ProductGroup] = SC.[ProductGroup]
   ,Dest.[Division] = SC.[DivisionName]
   ,Dest.[VendorNo] = SC.[Vendor No_]
   ,Dest.[VendorItemNo] = SC.[Vendor Item No_]
   ,Dest.[InventoryPostingGroup] = SC.[Inventory Posting Group]
   ,Dest.[BaseUnitOfMeasure] = SC.[Base Unit of Measure]
   ,Dest.[Item Capacity Value] = SC.[Item Capacity Value]
   ,Dest.[UnitPrice] = SC.[Unit Price]
   ,Dest.[LastItemCost] = SC.[LastItemCost]
   ,Dest.[RowID] = SC.[RowID]
   ,Dest.[BatchDate] = SC.[BatchDate]
FROM [DW].[dItem] Dest
INNER JOIN #TEMPstagin_item SC
	ON Dest.[No] = SC.[No]
	AND Dest.[Company] = SC.[Company]

SELECT
	'RowCount' = @@rowcount
GO

